﻿using ASmile.ORM.Cores;
using ASmile.ORM.Examples.DBModel;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;

namespace ASmile.ORM.Examples
{
    class Program
    {
        static void Main(string[] args)
        {
            //1. 引用nuget 包, Install-Package ASmile.ORM
            DBContext db = new DBContextDebug(EmDbType.SQLServer, "Server=.;Database=asmile-examples;Trusted_Connection=True;");

            //Insert01(db);
            //Insert02(db);
            //Insert03(db);
            //Insert04(db);

            //Update01(db);
            //Update02(db);
            //Update03(db);

            //Del01(db);
            //Del02(db);

            //Query01(db);
            //Query02(db);
            //Query03(db);
            //Query04(db);
            //Query05(db);
            //Query06(db);

            //Proc01(db);
            //Proc02(db);
            Proc03(db);
            Console.ReadKey();

        }

        public static void PrintLog(string log)
        {
            Console.WriteLine($"[{DateTime.Now:HH:mm:ss.fff}]: {log}");

        }

        #region 新增 示例


        //第一种新增，使用代码生成器生成的 实体类新增
        static void Insert01(DBContext db)
        {
            //DBContext db = new DBContextDebug(EmDbType.SQLServer, "Server=.;Database=asmile-examples;Trusted_Connection=True;");

            var r =
            db.Insert(new Test01
            {
                ID = 1,
                Name = "张三",
                Age = 19,
                Birthday = new DateTime(1999, 9, 9),
                Balance = 8765.43m,
                AddTime = DateTime.Now,
                Remark = ""
            });
            PrintLog($"新增完成，\nSqlContent:\n{r.SqlContent}");
        }

        //匿名对象新增
        static void Insert02(DBContext db)
        {

            var r =
            db.Insert("Test01",
            new
            {
                ID = 2,
                Name = "李四",
                Age = 19,
                Birthday = new DateTime(1990, 5, 5),
                Balance = 9000,
                AddTime = DateTime.Now,
                Remark = "匿名对象新增"
            });
            PrintLog($"新增完成，\nSqlContent:\n{r.SqlContent}");
        }

        //未赋值则忽略测试
        static void Insert03(DBContext db)
        {
            var r =
            db.Insert(
            new Test01
            {
                ID = 3,
                Name = "王五",
                //Age = 19,
                Birthday = new DateTime(1990, 5, 5),
                Balance = 9000,
                AddTime = DateTime.Now,
                Remark = "年龄未赋值"
            });
            PrintLog($"新增完成，\nSqlContent:\n{r.SqlContent}");
        }

        // 批量新增，批量新增必须赋值所有非空字段，不能取数据库默认值
        static void Insert04(DBContext db)
        {
            List<Test01> addList = new List<Test01>();
            for (int i = 0; i < 20; i++)
            {
                addList.Add(new Test01
                {
                    ID = 4 + i,
                    Name = $"Name {i + 1}",
                    Age = 22,
                    Birthday = new DateTime(2000, 8, 8),
                    Balance = 1999.56m,
                    AddTime = DateTime.Now,
                    Remark = "批量新增"
                });
            }
            db.InsertBulk(addList);
            PrintLog($"批量新增完成");
        }

        #endregion

        #region 修改示例

        static void Update01(DBContext db)
        {
            //将 ID 是 5 的姓名该为 刘德华
            var r =
            db.Update(new Test01
            {
                ID = 5,
                Name = "刘德华",
                Remark = ""
            });
            PrintLog($"修改完成，\nSqlContent:\n{r.SqlContent}");
        }

        static void Update02(DBContext db)
        {
            //通过 lamda表达式修改，
            var r =
            db.Update(
            new Test01
            {
                Balance = 500,
                Name = "刘玄德",
                Remark = "lamda修改"
            }, s => s.Name == "Name 3");
            PrintLog($"修改完成，\nSqlContent:\n{r.SqlContent}");
        }

        static void Update03(DBContext db)
        {
            //SQL文本赋值方式更新
            var r =
            db.UpdateByExpr<Test01>(s => s.Age < 20,
            "Balance = Balance + 250"
            );
            PrintLog($"修改完成，\nSqlContent:\n{r.SqlContent}");
        }

        #endregion

        #region 删除

        static void Del01(DBContext db)
        {
            //按实体类主键删除
            var r =
            db.Delete(new Test01
            {
                ID = 7
            });
            PrintLog($"删除完成，\nSqlContent:\n{r.SqlContent}");
        }

        static void Del02(DBContext db)
        {
            //使用lamda表达式条件删除
            var r =
            db.Delete<Test01>(s => s.Name == "Name 10");
            PrintLog($"删除完成，\nSqlContent:\n{r.SqlContent}");
        }

        #endregion

        #region 查询

        static void PrintObjectProperties(object obj)
        {
            Type type = obj.GetType();
            PropertyInfo[] properties = type.GetProperties();

            foreach (PropertyInfo property in properties)
            {
                Console.WriteLine($"{property.Name}: {property.GetValue(obj, null)}");
            }
        }

        static void Query01(DBContext db)
        {
            // lamda 表达式 查询首行数据
            var r = db.Query<Test01>(s => s.ID == 5);
            var m = r.ToEntity();
            PrintLog($"查询完成，\nSqlContent:\n{r.SqlContent}");

            Console.WriteLine();
            Console.WriteLine("属性值：");
            PrintObjectProperties(m);
        }

        static void Query02(DBContext db)
        {
            // lamda 表达式 查询列表
            var r = db.Query<Test01>(s => s.WEx_Like(s.Name, "Name%") && s.ID < 16);
            var m = r.ToList();
            PrintLog($"查询完成，\nSqlContent:\n{r.SqlContent}");

        }

        static void Query03(DBContext db)
        {
            // Query对象 灵活查询
            var query = db.CreateQuery<Test01>();
            string name = "name%";
            if (name != "")
            {
                query.WhereAnd(s => s.WEx_Like(s.Name, $"{name}"));
            }
            var r = db.Query(query);
            var m = r.ToList();
            PrintLog($"查询完成，\nSqlContent:\n{r.SqlContent}");
        }

        static void Query04(DBContext db)
        {
            // 使用 Query 委托对象 灵活查询
            string name = "name%";
            var r = db.Query<Test01>(query =>
            {
                if (name != "")
                {
                    query.WhereAnd(s => s.WEx_Like(s.Name, $"{name}"));
                }
            });
            var m = r.ToList();
            PrintLog($"查询完成，\nSqlContent:\n{r.SqlContent}");
        }

        static void Query05(DBContext db)
        {
            // 分页查询
            string name = "name%";
            var r = db.Query<Test01>(query =>
            {
                query.PageNo = 1;
                query.PageSize = 6;
                if (name != "")
                {
                    query.WhereAnd(s => s.WEx_Like(s.Name, $"{name}"));
                }
                query.OrderBy(s => s.Name); // 姓名排序
            });
            var m = r.ToList();
            PrintLog($"查询完成，\nSqlContent:\n{r.SqlContent}");
        }

        static void Query06(DBContext db)
        {
            //使用sql文本查询（内置 sql 工具）
            var r =
            db.Query(sql =>
            {
                int minAge = 15;
                int maxAge = 22;
                sql.AddSQLParam("select * from Test01 where age > {0} and age < {1}", minAge, maxAge);
            });
            var rData = r.ToDataTable();  // 返回 DataTable 格式
            var rList = r.ToList<Test01>(); // 这里 List 泛型可以使用任何类            
            PrintLog($"查询完成，\nSqlContent:\n{r.SqlContent}");
        }

        #endregion

        #region 存储过程

        static void Proc01(DBContext da)
        {
            //存储过程查询
            List<Test01> rList =
            da.ExecProcedure("P_Query01", new
            {
                Name = "张三"
            }).ToList<Test01>();

            PrintLog($"存储过程执行完成");
        }

        static void Proc02(DBContext da)
        {
            //存储过程执行
            da.ExecProcedure("P_Exec01", new
            {
                Name = "张三"
            }).ExecProc();

            PrintLog($"存储过程执行完成");
        }

        class OutData
        {
            public string OutMsg { set; get; }
        }

        static void Proc03(DBContext da)
        {
            // 执行存储过程并返回输出参数值
            var retProc =
            da.ExecProcedure("P_Exec02", new
            {
                Name = "张三"
            });
            OutData outData = new OutData();
            retProc.ExecProc(outData);
            var msg = outData.OutMsg;

            PrintLog($"存储过程执行完成, {msg}");
        }

        static void Proc04(DBContext da)
        {
            // 执行存储过程并返回输出参数值，匿名对象返回
            var retProc =
            da.ExecProcedure("P_Exec02", new
            {
                Name = "张三"
            });
            retProc.ExecProc(new { OutMsg = "" });
            var msg = retProc.OutObject.OutMsg;

            PrintLog($"存储过程执行完成, {msg}");
        }

        #endregion
    }
}
